﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class FuelMachinas2_monthObject
    {
        public const string sql = @"
SELECT	convert(nchar(2),total.month) as Месяц,
		convert(nchar(4),total.year) as Год,
		convert(nchar(20),DictionaryMachines.Name) as Машина,
		convert(nchar(20),COALESCE(convert(nchar,FirstLastFuelRemaining.FirstFuelRemaining),0)) as Первый__ост_в_баке,
		convert(nchar(20),COALESCE(total.Liters,0)) as Заправлено__л,
		convert(nchar(20),COALESCE(FirstLastFuelRemaining.LastFuelRemaining,0)) as Посл__ост_в_баке,
		convert(nchar(20),COALESCE(total.Distance,0)) as Пробег_км,
		convert(nchar(20),Round((((FirstLastFuelRemaining.FirstFuelRemaining + total.Liters - FirstLastFuelRemaining.LastFuelRemaining) * 100) / total.Distance),2)) as Расход,
		'(Первый остаток в баке + Всего заправлено - Последний остаток в баке) х100 / Всего пробег = Расход (л/100 км)' as Формула
FROM (
	Select r.month as month
		  ,r.year as year
		  ,r.machine as machine
		  ,sum(r.Liters) as liters
		  ,sum(r.Distance) as distance 
  	From (select DATEPART(month, refueling.Date) as month, DATEPART(year, refueling.Date) as year, machine, liters, distance, FuelOperType 
  		  from Refueling) as r 
  	Where r.FuelOperType = 2 
  	Group by r.month, r.year, r.machine) AS total
LEFT JOIN DictionaryMachines on total.Machine = DictionaryMachines.ID
LEFT JOIN (	Select idr.month as month, idr.year as year, idr.Machine as machine, Refueling_min.FuelRemaining as FirstFuelRemaining, Refueling_max.FuelRemaining as LastFuelRemaining
					From (SELECT MinID.month, MinID.year, MinID.machine, MinID.min_id, MaxID.max_id
		FROM (	SELECT r2.month as month, r2.year as year, r2.machine as machine, min (r2.id) as min_id
				FROM (SELECT id, Date, DATEPART(month, refueling.Date) as month, DATEPART(year, refueling.Date) as year, machine FROM Refueling) as r2
							lEFT JOIN 
								(SELECT r1.month, r1.year, r1.machine, min (r1.date) as min_date, max (r1.date) as max_date
								FROM (SELECT id, Date, DATEPART(month, refueling.Date) as month, DATEPART(year, refueling.Date) as year, machine FROM Refueling) as r1
								GROUP BY r1.machine, r1.month, r1.year) as MinMaxDate on r2.month = MinMaxDate.month and 
																						 r2.year = MinMaxDate.year and 
																						 r2.machine = MinMaxDate.machine
				Where r2.Date = MinMaxDate.min_date
				GROUP BY r2.machine, r2.month, r2.year ) as MinID
		LEFT JOIN (	SELECT r2.month, r2.year, r2.machine, max (r2.id) as max_id
					FROM (SELECT id, Date, DATEPART(month, refueling.Date) as month, DATEPART(year, refueling.Date) as year, machine FROM Refueling) as r2
								lEFT JOIN 
									(SELECT r1.month, r1.year, r1.machine, min (r1.date) as min_date, max (r1.date) as max_date
									FROM (SELECT id, Date, DATEPART(month, refueling.Date) as month, DATEPART(year, refueling.Date) as year, machine FROM Refueling) as r1
									GROUP BY r1.machine, r1.month, r1.year) as MinMaxDate on r2.month = MinMaxDate.month and 
																							 r2.year = MinMaxDate.year and 
																							 r2.machine = MinMaxDate.machine
					Where r2.Date = MinMaxDate.max_date
					GROUP BY r2.machine, r2.month, r2.year ) as MaxID
		ON 	MinID.month = MaxID.month and
			MinID.year = MaxID.year and
			MinID.machine = MaxID.machine) as idr
			Left join Refueling as Refueling_min on idr.min_id = Refueling_min.id
			Left join Refueling as Refueling_max on idr.max_id = Refueling_max.id
		  ) as FirstLastFuelRemaining 
ON 	total.month = FirstLastFuelRemaining.month and
	total.year = FirstLastFuelRemaining.year and
	total.machine = FirstLastFuelRemaining.Machine
";
        public string Месяц { get; set; }
        public string Год { get; set; }
        public string Машина { get; set; }
        public string Первый__ост_в_баке { get; set; }
        public string Заправлено__л { get; set; }
        public string Посл__ост_в_баке { get; set; }
        public string Расход { get; set; }
        public string Формула { get; set; }
    }
}
